package com.yita.demo02_project.dao.Impl;

import com.yita.demo02_project.dao.FruitDAO;
import com.yita.demo02_project.pojo.Fruit;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class FruitDAOImpl implements FruitDAO {
    private Connection conn;
    private PreparedStatement psmt;
    private ResultSet rs;

    final String DRIVER = "com.mysql.cj.jdbc.Driver";
    final String URL = "jdbc:mysql://localhost:13306/fruitdb";
    final String USER = "root";
    final String PWD = "root";


    @Override
    public List<Fruit> getFruitList()  {
        try {
            Class.forName(DRIVER);
            conn= DriverManager.getConnection(URL, USER, PWD);
            String sql = "select * from t_fruit";
            psmt= conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            ArrayList<Fruit> fruits = new ArrayList<>();
            while (rs.next()){
                Fruit fruit = new Fruit();
                fruit.setFid(rs.getInt("fid"));
                fruit.setFname(rs.getString("fname"));
                fruit.setPrice(rs.getInt("price"));
                fruit.setFcount(rs.getInt("fcount"));
                fruit.setRemark(rs.getString("remark"));
                fruits.add(fruit);
            }
            return fruits;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if(psmt!=null){
                    psmt.close();
                }
                if(conn!=null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return null;
    }

    @Override
    public boolean addFruit(Fruit fruit) {
        try {
            Class.forName(DRIVER);
            conn= DriverManager.getConnection(URL, USER, PWD);
            String sql = "insert into t_fruit values (0,?,?,?,?)";
            psmt= conn.prepareStatement(sql);
            psmt.setString(1, fruit.getFname());
            psmt.setInt(2, fruit.getPrice());
            psmt.setInt(3, fruit.getFcount());
            psmt.setString(4, fruit.getRemark());
            int i = psmt.executeUpdate();
            return i>0;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if(psmt!=null){
                    psmt.close();
                }
                if(conn!=null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public boolean updateFruit(Fruit fruit) {
        try {
            Class.forName(DRIVER);
            conn= DriverManager.getConnection(URL, USER, PWD);
            String sql = "update t_fruit set fcount=? where fid=?";
            psmt= conn.prepareStatement(sql);
            psmt.setInt(1, fruit.getFcount());
            psmt.setInt(2, fruit.getFid());
            int i = psmt.executeUpdate();
            return i>0;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if(psmt!=null){
                    psmt.close();
                }
                if(conn!=null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    @Override
    public Fruit getFruitByFname(String fname) {
        try {
            Class.forName(DRIVER);
            conn= DriverManager.getConnection(URL, USER, PWD);
            String sql = "select * from t_fruit where fname=? limit 0,1";
            psmt= conn.prepareStatement(sql);
            psmt.setString(1, fname);
            rs = psmt.executeQuery();

            while (rs.next()) {
                Fruit fruit = new Fruit();
                fruit.setFname(fname);
                fruit.setFid(rs.getInt("fid"));
                fruit.setRemark(rs.getString("remark"));
                fruit.setPrice(rs.getInt("price"));
                fruit.setFcount(rs.getInt("fcount"));
                return fruit;
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if(psmt!=null){
                    psmt.close();
                }
                if(conn!=null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public boolean delFruit(String fname) {
        try {
            Class.forName(DRIVER);
            conn= DriverManager.getConnection(URL, USER, PWD);
            String sql = "delete from t_fruit where fname=?";
            psmt= conn.prepareStatement(sql);
            psmt.setString(1, fname);
            int i = psmt.executeUpdate();
            return i > 0;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if(psmt!=null){
                    psmt.close();
                }
                if(conn!=null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}
